<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Illuminate\Support\Facades\Redis;

use Swoole\Coroutine;
use Swoole\Timer;
use Swoole\Coroutine\Http\Client;
use Swoole\Coroutine\Channel;
use Swoole\Coroutine\MySQL;

use function Swoole\Coroutine\run;

use App\Models\Cabinet;
use App\Models\CabinetPoint;
use App\Models\CabinetDataSecondBackup;

class DeleteWeconDataEveryWeek extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'delete-wecon-data-every-week';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'description';

    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct()
    {
        parent::__construct();
    }

    /**
     * Execute the console command. 
     *
     * @return mixed
     */
    public function handle()
    {
        Timer::tick(1000, function (int $timer_id) {
            $today = strtotime(date("Y-m-d")." 00:00:00");
            if (time() == $today) {
                $yesterday = strtotime("-1 day");
                $swooleMysql = new MySQL();
                $swooleMysql->connect([
                    'host'     => env('DB_HOST'),
                    'port'     => env('DB_PORT'),
                    'user'     => env('DB_USERNAME'),
                    'password' => env('DB_PASSWORD'),
                    'database' => env('DB_DATABASE'),
                    'fetch_mode' => true,
                ]);

                $cabinets = Cabinet::select('id')->where('box_id', '>', 0)->where('group_id', '>', 0)->where('show', Cabinet::SHOW_YES)->get()->toArray();
                foreach ($cabinets as $oneCabinet) {
                    $name = Cabinet::where('id', $oneCabinet['id'])->value('name');
                    $points = CabinetPoint::where('cabinet_id', $oneCabinet['id'])->pluck('name', 'alias')->toArray(); 
                    $querySql = 'SELECT `data`,`created_time` FROM `xfy_cabinet_data_second` WHERE `cabinet_id` = '.$oneCabinet['id'].' AND `created_time` > '.$yesterday.' AND `created_time` < '.time();
                    self::genXsl($swooleMysql, $querySql, $oneCabinet['id'], $name, $points);
                    $delSql = 'DELETE FROM `xfy_cabinet_data_second` WHERE `cabinet_id` = '.$oneCabinet['id'].' AND `created_time` < '.strtotime("-7 day");
                    $swooleMysql->query($delSql);
                }
            }
        });
        \Swoole\Event::wait();
        return;
    }

    public static function genXsl($swooleMysql, $querySql, $cabinetId, $name, $points){
        $date = date("Y-m-d", strtotime("-1 day"));
        $path = public_path(CabinetDataSecondBackup::CATALOGUE).'/'.$date;
        if (!is_dir($path.'/')) {
            mkdir($path);
            chmod($path, 0777);
        }
        clearstatcache();
        $fileName = $name.'.csv';
        $file = $path.'/'.$fileName;
        $fp = fopen($file, 'a');
        fwrite($fp, chr(0xEF).chr(0xBB).chr(0xBF));

        $header = ['时间'];
        foreach ($points as $alias => $onePoint) {
            $header[] = $onePoint;
        }
        fputcsv($fp, $header);

        $stmt = $swooleMysql->prepare($querySql);
        $rr = $stmt->execute();
        while ($ret = $stmt->fetch()) {
            $row = [];
            $row[] = date('Y-m-d H:i:s', $ret['created_time']);
            $d = json_decode($ret['data'], true);
            foreach ($points as $alias => $onePoint) {
                if (isset($d[$alias])) {
                    $row[] = $d[$alias];
                }
            }
            fputcsv($fp, $row);
        }
        fclose($fp);

        $insertSql = 'INSERT INTO `xfy_cabinet_data_second_backup`(`cabinet_id`, `name`, `date`, `created_at`, `updated_at`) VALUES ('.$cabinetId.', "'.$name.'", "'.$date.'", "'.date('Y-m-d H:i:s').'", "'.date('Y-m-d H:i:s').'")';
        $swooleMysql->query($insertSql);
    }
}
